package com.xy_pro.xymanager.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.xy_pro.xymanager.entity.ScoreData;
import com.xy_pro.xymanager.dto.ScoreTitleDTO;
import com.xy_pro.xymanager.entity.UserEntity;
import com.xy_pro.xymanager.exception.ExcelProcessingException;
import com.xy_pro.xymanager.payload.UploadResponse;
import com.xy_pro.xymanager.repository.ScoreDataRepository;
import com.xy_pro.xymanager.repository.UserRepository;
import com.xy_pro.xymanager.validator.ExcelValidator;
import io.micrometer.common.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ScoreService {

    @Autowired
    private ScoreDataRepository scoreDataRepository;

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    public UploadResponse processExcel(String tile, MultipartFile file) {
        UploadResponse response = new UploadResponse();
        List<String> errorMessages = new ArrayList<>();
        int successfulRows = 0;
        int failedRows = 0;

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if(sheet == null || sheet.getLastRowNum() == 0){
                errorMessages.add("excel表格的第一张Sheet不能为空");
                response.setErrorMessages(errorMessages);
                return  response;
            }
            Iterator<Row> rowIterator = sheet.iterator();

            // 获取并验证表头
            Row headerRow = rowIterator.next();
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }

            List<String> headerErrors = ExcelValidator.validateHeaders(headerRow);
            if (!headerErrors.isEmpty()) {
                throw new ExcelProcessingException("表头验证失败: " + String.join("; ", headerErrors));
            }

            // 处理数据行
            int rowNum = 2; // 从第2行开始(表头是第1行)
            while (rowIterator.hasNext()) {
                Row currentRow = rowIterator.next();
                List<Map<String, Object>> orderedList = new ArrayList<>();
                List<String> rowErrors = new ArrayList<>();
                String studentName = "";
                // 验证每个单元格
                for (int i = 0; i < headers.size(); i++) {
                    Map<String, Object> rowData = new LinkedHashMap<>();
                    String header = headers.get(i);
                    Cell cell = currentRow.getCell(i);

                    String error = ExcelValidator.validateCell(cell, header);
                    if (error != null) {
                        rowErrors.add("第" + rowNum + "行, 列[" + header + "]: " + error);
                    }
                    String value = getCellValue(cell);
                    System.out.println(header + ":" + value);
                    if(header.equals("学生姓名")){
                        studentName =value;
                    }
                    // 提取单元格值,强制转String
                    rowData.put(header, getCellValue(cell));
//                    rowData.put("v", );
                    orderedList.add(rowData);
//                    rowData.put(header, (String)getCellValue(cell));
                }

                // 检查学生姓名是否存在
//
//                if (studentName != null && !studentName.isEmpty()) {
//                    Optional<UserEntity> studentOptional = userRepository.findByName(studentName);
//                    if (!studentOptional.isPresent()) {
//                        rowErrors.add("第" + rowNum + "行: 学生姓名 '" + studentName + "' 在系统中不存在");
//                    }
//                }

                Date currentDate = new Date();
                String jsonArrayString = new ObjectMapper().writeValueAsString(orderedList);

                System.out.println("jsonArrayString==========:" + jsonArrayString);
                // 处理验证结果
                if (rowErrors.isEmpty()) {
                    // 验证通过，保存数据
                    ScoreData scoreData = new ScoreData();
                    scoreData.setData(jsonArrayString);
//                    System.out.println(rowData);
                    scoreData.setCreateTime(currentDate);
                    scoreData.setTitle(tile);
                    scoreData.setName(studentName);
                    scoreDataRepository.save(scoreData);
                    successfulRows++;
                } else {
                    // 验证失败，记录错误
                    failedRows++;
                    errorMessages.addAll(rowErrors);
                }

                rowNum++;
            }

            // 构建响应
            response.setTotalRows(sheet.getLastRowNum());
            response.setSuccessfulRows(successfulRows);
            response.setFailedRows(failedRows);
            response.setErrorMessages(errorMessages);

        } catch (IOException e) {
            throw new ExcelProcessingException("处理Excel文件时发生IO错误", e);
        }

        return response;
    }

    public ScoreData getUserScoreData(String userName){
        logger.info("获取学生成绩： {}",userName);
        Optional<ScoreData> optional = scoreDataRepository.findTopByNameOrderByCreateTimeDesc(userName);
        if (optional.isPresent()) {
            ScoreData data = optional.get();
            if(data.isVisible()){
                return data;
            } else  {
                return null;
            }
        } else  {
            return null;
        }
    }

    private String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return BigDecimal.valueOf(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue() + "";
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }


    public Page<ScoreTitleDTO> getScoreList(String title, int currentPage, int pageSize) {

        Pageable pageable = PageRequest.of(currentPage - 1, pageSize);

        if(title == null || title.isEmpty()){
            // 方式2：查询所有不重复标题
            return scoreDataRepository.findDistinctTitle(pageable);

        } else {
            // 方式1：带条件查询
            logger.info("带条件查询 {} ", title);
            return scoreDataRepository.findDistinctByTitle(title, pageable);
//            return titles2.map(ScoreTitleDTO::new);
        }
    }


}
